import pymssql
import deptconfig

server = '172.16.3.19'
port = '2019'
user = 'sa'
password = '*#zw8'
database = 'TOP_ERP_SUT'
dept = deptconfig.depts[deptconfig.dept_index]

try:

    # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
    db = pymssql.connect(server=server, port=port, user=user, password=password, database=database)
    print("数据库连接成功")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
except pymssql.Error as e:
    print("数据库连接失败：" + str(e))

'''查询视图时添加是否是单双周字段 IsSDWeek   2024.5.6   SPF'''

# 教学任务信息
def init_course_task():
    task_course = []
    sql = f''' SELECT DISTINCT A.Optno,B.CourseID,B.CourseName,B.Teacher, B.TutorName,B.Class,C.GraduateID,WeekStart,WeekEnd,WeekHour,
    B.StudyHour,B.IsSDWeek from nTB_ArrClass_info as A LEFT JOIN VB_Score_Manage as B ON A.Optno= B.Sno left Join VB_SelectCourse_Base as C on C.Sno = B.Sno
    where B.year='2023-2024' and B.term='1' and B.WeekStart is not null and B.WeekEnd is not null and C.GraduateID is not null
    and B.StudyHour is not null and ISNULL(A.SSno, '') = '' and B.DeptID in ({dept}) '''
    try:
        cursor.execute(sql)
        results_cou = cursor.fetchall()

        # 处理初始数据（转换为带学生的教学计划）
        for cou in results_cou:
            task_found = False
            for task in task_course:
                if task[0] == str(cou[0]):
                    task[6].append(cou[6])
                    task_found = True
                    break
            if not task_found:
                task_course.append(
                    [str(cou[0]), cou[1], cou[2], cou[3], cou[4], cou[5], [cou[6]], cou[7], cou[8],
                     cou[10] // (cou[8] - cou[7] + 1), cou[10],
                     cou[11]])
    except Exception as e:
        print(e)
    return task_course

# 学生信息
def init_sel_students():
    sel_stu_info = []
    sql = f''' SELECT DISTINCT A.GraduateID from VB_SelectCourse_Base as A LEFT JOIN nTB_ArrClass_info as B ON A.Sno= B.Optno
        left Join VB_Score_Manage as C on C.Sno = A.Sno where C.year='2023-2024' and C.term='1' and C.WeekStart is not null 
        and C.WeekEnd is not null and A.GraduateID is not null and C.StudyHour is not null and ISNULL(B.SSno, '') = '' and C.DeptID in ({dept}) '''
    try:
        cursor.execute(sql)
        results_stu = cursor.fetchall()
        for stu in results_stu:
            sel_stu_info.append(stu)
    except Exception as e:
        print(e)
    return sel_stu_info

# 教师信息
def init_sel_teachers():
    sel_tea_info = []
    sql = f''' SELECT DISTINCT A.Teacher from VB_Score_Manage as A LEFT JOIN nTB_ArrClass_info as B ON A.Sno= B.Optno
        left Join VB_SelectCourse_Base as C on C.Sno = A.Sno where A.year='2023-2024' and A.term='1' and A.WeekStart 
        is not null and A.WeekEnd is not null and C.GraduateID is not null and A.StudyHour is not null and ISNULL(B.SSno, '') = '' '''
    try:
        cursor.execute(sql)
        results_tea = cursor.fetchall()
        for tea in results_tea:
            sel_tea_info.append(tea)
    except Exception as e:
        print(e)
    return sel_tea_info

# 教室信息
def init_classrooms():
    classroom_info = []
    # sql = "select ClassRoomID,PeopleNumber from VB_ClassRoom where ClassTypeName in ( '普通教室')"
    # 查询可用教室（之前是所有教室都可用）
    sql = f'''SELECT DISTINCT ClassRoomID,PeopleNumber,BuildingID FROM VB_ClassRoom 
    WHERE (DeptID in ({dept}) or isNull(DeptID, '') = '') and  ClassTypeName = '一般教室' AND (BuildingName <> '体育馆' 
    or BuildingName <> '图书馆') '''
    try:
        cursor.execute(sql)
        results_clr = cursor.fetchall()
        for clr in results_clr:
            classroom_info.append(clr)  # 将教室信息添加到classroom_info列表中
            # classroom_info = [[26，150][74,40],[2,80].......]
    except Exception as e:
        print(e)
    return classroom_info


# 教学楼信息
def init_building():
    building_info = []
    sql = '''SELECT BuildingID FROM dbo.nTB_Building_info'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            building_info.append(clr)
    except Exception as e:
        print(e)
    return building_info


# 教学楼距离表
def init_buildingDistance():
    buildingDistance_info = []
    sql = '''SELECT * FROM dbo.nTB_Buildings_Distance'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            buildingDistance_info.append(clr)
    except Exception as e:
        print(e)
    return buildingDistance_info


# 导入数据到数据库中
def InsertBatchToDB(result):
    del_sql = "TRUNCATE TABLE dbo.nTB_ArrClassPreNewAutoSd_info"
    int_sql = "insert into dbo.nTB_ArrClassPreNewAutoSd_info values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    try:
        cursor.execute(del_sql)
        cursor.executemany(int_sql, result)
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        db.close()
